package com.jr.dao.impl;

import com.jr.dao.IEmployeesDao;
import com.jr.pojo.Employees;
import com.jr.util.DBHelper;
import com.jr.util.PageHelper;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class EmployeesDaoImpl implements IEmployeesDao {

    DBHelper db = new DBHelper();

    @Override //员工信息添加
    public int insertEmployees(Employees employee) {
        /*(Integer employeeId, String name, Integer departmentId,
                     Integer positionId, Date hireDate, Timestamp createdAt,
                     Timestamp updatedAt, Integer age)*/
        String sql = "insert into employees values(null,?,?,?,?,?,?,?)";
        Object[] objs = {employee.getName(), employee.getDepartmentId(),
                employee.getPositionId(), employee.getHireDate(), employee.getCreatedAt(),
                employee.getUpdatedAt(), employee.getAge()};
        return db.dtUpdate(sql, objs);
    }

    @Override // 在 个人设置里 修改
    public int updateEmployees(Employees employee) {
        /* 薪资去salary表里改 */
        String sql = "update from employees set name=?,age=?,departmentId=?,positionId=?,hireDate=? where employeeId=? ";
        Object[] objs = {employee.getName(), employee.getAge(), employee.getDepartmentId(), employee.getPositionId(), employee.getHireDate(), employee.getEmployeeId()};
        return db.dtUpdate(sql, objs);
    }

    @Override //根据 员工ID 查询
    public Employees selectEmployeesByEmployeeId(Integer employeeId) {
        String sql = "select * from employees where employeeId=?";
        ArrayList<Employees> list = db.dtQuery(sql, Employees.class, employeeId);
        return list.size() > 0 ? list.get(0) : new Employees();
    }

    @Override //删除
    public int deleteEmployeesByEmployeeId(Integer employeeId) {
        String sql = "delete from employees where employeeId=?";
        return db.dtUpdate(sql, employeeId);
    }

    @Override // 分页查询 -- 总条数
    public int selectPageDataCount() {
        StringBuilder sql = new StringBuilder("select count(employeeId) from employees where 1=1");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }

    @Override // 分页查询 查询当前页面 展示集合
    public ArrayList<Employees> selectIndexDate(PageHelper ph) {
        String sql = "select * from employees limit ?,?";
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        return db.dtQuery(sql, Employees.class, index, ph.getShowNumber());
    }

    @Override // 分页查询 -- 按名字 查 -- 总条数
    public int selectPageDataCountDepartmentId(Integer departmentId) {
        StringBuilder sql = new StringBuilder("select count(employeeId) from employees where departmentId=?");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            ps.setObject(1, departmentId);
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }

    @Override // 分页查询 按部门Id 查询 展示集合
    public ArrayList<Employees> selectIndexDateByDepartmentId(Integer departmentId, PageHelper ph) {
        String sql = "select * from employees where departmentId=? limit ?,?";
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        return db.dtQuery(sql, Employees.class, departmentId, index, ph.getShowNumber());
    }

    @Override // 分页查询 -- 按名字 查 -- 总条数
    public int selectPageDataCountByName(String name) {
        StringBuilder sql = new StringBuilder("select count(name) from employees where name like ?");
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            con = db.getCon();
            ps = con.prepareStatement(sql.toString());
            ps.setObject(1, "%"+name+"%");
            resultSet = ps.executeQuery();
            if (resultSet.next()) {
                return resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            db.closeAll(con, ps, resultSet);
        }
        return 0;
    }

    @Override // 分页查询 按名字 查询 展示集合
    public ArrayList<Employees> selectIndexDateByName(String name, PageHelper ph) {
        String sql = "select * from employees where name like ? limit ?,?";
        int index = (ph.getPageIndex() - 1) * ph.getShowNumber();
        return db.dtQuery(sql, Employees.class, "%"+name+"%", index, ph.getShowNumber());
    }
}
